package DBLayer;
import ModelLayer.*;

import java.sql.*;
import java.util.ArrayList;

public class DBSalesOrder{
	private Connection con;
	public DBSalesOrder() {
	      con = DbConnection1.getInstance().getDBcon();
	}
	
	// get all sale orders
	public ArrayList<SalesOrder> getAllSalesOrders(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
	
	// find a sale order with a sale id
	public SalesOrder findSalesOrder(String saleId, boolean retriveAssociation){
		String wClause = "saleId= '" + saleId + "'";
		return singleWhere(wClause, retriveAssociation);
	}
	
	public int insertSalesOrder(SalesOrder salesOrder) throws Exception
    {  //call to get the next customerid number
        int nextID = GetMax.getMaxId("Select max(saleId) from salesorder");
        nextID = nextID + 1;
        System.out.println("next order = " +  nextID);
  
        int rc = -1;
	    String query="INSERT INTO salesorder(saleId, customerId, invoiceId, date, amount, deliveryStatus, deliveryDate)  VALUES("+
	  		nextID  + ",'"  +
		    salesOrder.getCustomerId()  + "','"  +
            salesOrder.getInvoiceId() + "','" +
		    salesOrder.getDate()  + "'," +
            salesOrder.getAmount()  + ",'" +
		    salesOrder.getDeliveryStatus()  + "','" +
            salesOrder.getDeliveryDate() + "')";
                     
        System.out.println("insert : " + query);
        try{ // insert new customer +  dependent
           Statement stmt = con.createStatement();
           stmt.setQueryTimeout(5);
     	   rc = stmt.executeUpdate(query);
           stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Salesorder not created");
          throw new Exception ("Salesorder is not inserted correct");
       }
       return(rc);
    }
	
	public int updateSalesOrder(SalesOrder salesOrder)
	{
		SalesOrder sOrObj  = salesOrder;
		int rc=-1;

		String query="UPDATE salesorder SET " +		 	  
              "customerId = '"+ sOrObj.getCustomerId() + "', " +
              "invoiceId = '"+ sOrObj.getInvoiceId() + "', " +
		      "date = '"+ sOrObj.getDate() + "', " +
              "amount = '"+ sOrObj.getAmount() + "', " +
		      "deliveryStatus = '"+ sOrObj.getDeliveryStatus() + "', " +
		      "deliveryDate = '"+ sOrObj.getDeliveryDate() + "', " +
              " WHERE saleId = '"+ sOrObj.getSaleId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteSalesOrder(String saleId)
	{
        int rc=-1;
	  
	  	String query="DELETE FROM salesorder WHERE saleId = '" +
				saleId + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in customer db: "+ex);
   	        }
		return(rc);
	}
	
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder sOrObj = new SalesOrder();
                
        String query =  buildQuery(wClause);
        System.out.println(query);
        
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
	 			sOrObj = buildSalesOrder(results);
                //association is to be build
                stmt.close();
                
                /*if(retrieveAssociation)
                {   
                    DBSalesOrder dbsalesObj = new DBSalesOrder();
                    custObj.setSalesOrderList(dbsalesObj.findCostumerSalesOrder(custObj.getCustomerId(), false));                   
                }*/
			}
            else{ //no invoice was found
            	sOrObj = null;
            }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return sOrObj;
	}
	
	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();	
		
	    String query =  buildQuery(wClause);
  
        try{ // read the invoice from the database
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);
	 	
	
			while( results.next() ){
				SalesOrder sOrObj = new SalesOrder();
		     	sOrObj = buildSalesOrder(results);	
		     	list.add(sOrObj);	
			}//end while
			
			stmt.close();  
			
			/*if(retrieveAssociation)
		    {   //The supervisor and department is to be build as well
				for(Employee empObj : list){
		            String superssn = empObj.getSupervisor().getSsn();
		            Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
		            empObj.setSupervisor(superEmp);
		            System.out.println("Supervisor is selected");
		           // here the department has to be selected as well
		        }
		    }*/   
				
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
        
		return list;
	}
	
	private String buildQuery(String wClause)
	{
	    String query="SELECT saleId, customerId, invoiceId, date, amount, deliveryStatus, deliveryDate FROM Salesorder";		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private SalesOrder buildSalesOrder(ResultSet results){
		SalesOrder sOrObj = new SalesOrder();
        try{ // the columns from the table costumer are used
              sOrObj.setSaleId(results.getInt("saleId"));
              sOrObj.setCustomerId(results.getString("customerId"));
              sOrObj.setInvoiceId(results.getString("invoiceId"));
              sOrObj.setDate(results.getString("date"));
              sOrObj.setAmount(results.getInt("amount"));
              sOrObj.setDeliveryStatus(results.getString("deliveryStatus"));
              sOrObj.setDeliveryDate(results.getString("deliveryDate"));
       }
       catch(Exception e)
       {
           System.out.println("Error in building the customer object");
       }
       return sOrObj;
    }
}
